﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace WindowsFormsApp1
{

    public class StuData
    {
       public string StuID { get; set; }
       public string StuName { get; set; }
       public string StuSex { get; set; }
       public string StuAge { get; set; }
       public string StuClassID { get; set; }
       public string StuClass { get; set; }
    }

    public class SQLmanager
    {
        static SqlConnection con = new SqlConnection("server=.;database=C;pwd=123456;uid=sa;");
        static SqlCommand com;
        static SqlDataReader dr;

        //查询
        public List<StuData>  Select (string name)
        {
            string cx1 = "1=1";
            if (name != "")
            {
                cx1 = "StuName like @name";
            }
            //拼接条件
            string cx = "select a.*,b.className from Student as a join Class as b on a.StuClassID=b.classID  where "+cx1;
            List<StuData> list = new List<StuData>();
            com = new SqlCommand(cx, con);
            com.Parameters.AddWithValue("@name", "%" + name + "%");
            con.Open();
            //读取数据
            dr = com.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    StuData da = new StuData();
                    da.StuID= dr[0].ToString();
                    da.StuName = dr[1].ToString();
                    da.StuSex = dr[2].ToString();
                    da.StuAge = dr[3].ToString();
                    da.StuClassID = dr[4].ToString();
                    da.StuClass = dr[5].ToString();
                    list.Add(da);
                }
            }
            con.Close();
            return list;
        }

        //删除
        public void DeleteC (string s)
        {
            try
            {
                com = new SqlCommand("delete from Class where className=@s", con);
                com.Parameters.AddWithValue("@s", s);
                con.Open();
                com.ExecuteNonQuery();
                con.Close();
            }
            finally { con.Close(); }
        }
        public void DeleteS(string s,int b)
        {
            try
            {
                com = new SqlCommand("delete from Student where StuName=@s and StuClassID=@b", con);
                com.Parameters.AddWithValue("@s", s);
                com.Parameters.AddWithValue("@b", b);
                con.Open();
                com.ExecuteNonQuery();
                con.Close();
            }
            finally { con.Close(); }
        }

        
        //修改数据
        public void Updata(List<object> list)
        {
            int sid = Int32.Parse(list[0].ToString());
            string name = list[1].ToString();
            int sex = Int16.Parse(list[2].ToString());
            int age = Int16.Parse(list[3].ToString());
            int cid = Int16.Parse(list[4].ToString());


            com = new SqlCommand("UPDATE Student SET   StuID=@sid,StuName=@name,StuSex=@sex,StuAge=@age,StuClassID=@cid where StuID=@sid", con);
            com.Parameters.AddWithValue("@sid", sid);
            com.Parameters.AddWithValue("@sex", sex);
            com.Parameters.AddWithValue("@age", age);
            com.Parameters.AddWithValue("@cid", cid);
            com.Parameters.AddWithValue("@name", name);
            con.Open();
            com.ExecuteNonQuery();
            con.Close();
        }
        public void UpdataC(List<object> list)
        {
            int sid = Int16.Parse(list[0].ToString());
            string name = list[1].ToString();
            com = new SqlCommand("UPDATE Class SET classID=@sid,className=@name  where classID=@sid", con);
            com.Parameters.AddWithValue("@sid", sid);
            com.Parameters.AddWithValue("@name", name);
            con.Open();
            com.ExecuteNonQuery();
            con.Close();
        }

        //登入
        public bool DR(string s, int b)
        {
            try
            {
                com = new SqlCommand("select * from Student where StuName=@s and StuId=@b", con);
                com.Parameters.AddWithValue("@b", b);
                com.Parameters.AddWithValue("@s", s);
                con.Open();
                if (com.ExecuteScalar().ToString() != "")
                {
                    return true;
                }
                else { return false; }
            }catch (Exception x)
            {
                return false;
            }
            finally { con.Close(); }
        }

    }
}
